package com.yang.doctorrecruitment.dao;

import com.yang.doctorrecruitment.dto.Apply;
import com.yang.doctorrecruitment.dto.ReleaseJob;
import com.yang.doctorrecruitment.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ApplyDAO {
    private static Connection conn;
    public static boolean InsertApply(Apply apply){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        String sql="insert into apply values(?,?,?,?,?,?)";

        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,apply.getUserID());
            statement.setString(2,apply.getJobID());
            statement.setString(3,apply.getResumeAddress());
            statement.setInt(4,apply.getResumeKind());
            statement.setString(5,apply.getResumeTime());
            statement.setString(6,apply.getReply());
            int count=statement.executeUpdate();
            if(count>=1){
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }

    public static boolean ApplyExist(Apply apply){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        String sql="select * from apply where UserID=? and JobID=?";

        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,apply.getUserID());
            statement.setString(2,apply.getJobID());;
            res=statement.executeQuery();
            while(res.next()){
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }

    public static List<Apply> ApplyByUserID(String UserID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        String sql="select * from apply where UserID=?";

        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,UserID);
            res=statement.executeQuery();
            List<Apply> apply=new ArrayList<>();
            while(res.next()){
               Apply app=new Apply();
               app.setUserID(UserID);
               app.setJobID(res.getString("JobID"));
               app.setResumeKind(res.getInt("ResumeKind"));
               app.setResumeAddress(res.getString("ResumeAddress"));
               app.setResumeTime(res.getString("Resumetime"));
               app.setReply(res.getString("Reply"));
               apply.add(app);
            }
            return apply;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }

    public static List<ReleaseJob> ApplyJobByUserID(String UserID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        String sql="select * from job where JobID in (select JobID from apply where UserID=?)";
//通过这条语句能够查询出来所有的job,
        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,UserID);
            res=statement.executeQuery();
            List<ReleaseJob> list=new ArrayList<>();
            while(res.next()){
                ReleaseJob Job=new ReleaseJob();
                Job.setJobName(res.getString("Job"));
                Job.setJobID(res.getInt("JobID")+"");
                Job.setJobNum(res.getInt("JobNum"));
                Job.setPublishTime(res.getDate("PublishTime")+"");
                Job.setJobSalary(res.getInt("JobSalary"));
                Job.setJobAddress(res.getString("JobAddress"));
                Job.setJobRequire(res.getString("JobRequire"));
                Job.setUserID(res.getString("UserID"));
                Job.setJobDuty(res.getString("JobDuty"));
                list.add(Job);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }

    public static List<Apply> ComByJobID(String UserID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        String sql="select * from apply where JobID in (select JobID from job where UserID=?)";

        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,UserID);
            res=statement.executeQuery();
            List<Apply> apply=new ArrayList<>();
            while(res.next()){
                Apply app=new Apply();
                app.setUserID(res.getString("UserID"));
                app.setJobID(res.getString("JobID"));
                app.setResumeKind(res.getInt("ResumeKind"));
                app.setResumeAddress(res.getString("ResumeAddress"));
                app.setResumeTime(res.getString("Resumetime"));
                app.setReply(res.getString("Reply"));
                apply.add(app);
            }
            return apply;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return null;
    }

    public static boolean UpdataReply(String reply,String UserID,String JobID){
        conn= DBManager.getconnection();
        ResultSet res=null;
        PreparedStatement statement=null;
        String sql="update apply set Reply=? where UserID=? and JobID=?";

        try{
            statement=conn.prepareStatement(sql);
            statement.setString(1,reply);
            statement.setString(2,UserID);
            statement.setString(3,JobID);
            int count=statement.executeUpdate();
           if(count>=1){
               return true;
           }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBManager.closeAll(conn,statement,res);
        }
        return false;
    }

}
